#!/usr/bin/env ruby
#encoding: utf-8

module DB_VTB
  
  require 'sqlite3'
  require 'singleton'
  
  # global var for database path
  $VALUABLE_TO_BUY_DB = "valuable_to_buy.db"
  
  # this class is only used to create table if not exist
  # check db_impl.rb for db manipulations
  # I plan to move table creatation into db_impl.rb
  class SqliteImpl
    
    #mixin the singleton module
    # include Singleton
    
    # NOTE: though @db is used, I do not keep the database connection
    # Database connection will be opened and closed for each database operation
    # This behavior may be changed if it have a large group of users and the performance is degraded
    
    def initialize
      begin
        doTransaction { |db|
          db.execute CREATE_USER_TABLE
          db.execute CREATE_INTEREST_TABLE
          db.execute CREATE_NOFIFY_TABLE
        }
      rescue => e
        puts "Error: #{e.class} , #{e.message}"
        raise
      end
      
      # begin
        # @db = SQLite3::Database.open VALUABLE_TO_BUY_DB
#         
        # @db.transaction
        # @db.execute CREATE_USER_TABLE
        # @db.execute CREATE_INTEREST_TABLE
        # @db.execute CREATE_NOFIFY_TABLE
        # # @db.execute CASCADE_ON
        # @db.commit
      # rescue => e
        # # This is a fetal error
        # puts "Error: #{e.class} , #{e.message}"
        # @db.rollback
        # raise
      # ensure
        # @db.close unless @db.nil?
      # end
    end
    
    # def getAllUsers
      # doTransaction { |db|
        # users = db.execute("select * from user;")
      # }
      # users
    # end
#     
    # def getInterestsByUser
      # doTransaction { |db|
        # interests = db.execute("select * from interest where uid = ?;", user.uid)
      # }
      # interests
    # end
    
    private
    
    # define some private constant values
    CREATE_USER_TABLE = "create table if not exists users(
                            id integer primary key autoincrement, 
                            email text unique on conflict fail,
                            name text,
                            password text);"
    
    CREATE_INTEREST_TABLE = "create table if not exists interests(
                              id integer primary key autoincrement,
                              uid integer,
                              value text not null,
                              notified integer default 0,
                              is_valid integer default 1,
                              foreign key (uid) references users(id) on delete cascade on update cascade,
                              unique (uid, value) on conflict fail);"
                              
    CREATE_NOFIFY_TABLE = "create table if not exists notifies(
                            id integer primary key autoincrement,
                            iid integer,
                            url text not null,
                            time text not null,
                            foreign key (iid) references interests(id) on delete cascade on update cascade);"
    
    CASCADE_ON = "PRAGMA foreign_keys = ON;"
    
    CASCADE_OFF = "PRAGMA foreign_keys = OFF;"
    
    def doTransaction
      if block_given?
        begin
          @db = SQLite3::Database.open $VALUABLE_TO_BUY_DB
          # puts @db.encoding
          @db.transaction        
          @db.execute CASCADE_ON
          
          yield @db
          
          @db.commit
        rescue => e
          # This is a fetal error
          puts "Error: #{e.class} , #{e.message}"
          @db.rollback
          raise
        ensure
          @db.close unless @db.nil?
        end
      end
    end
    
  end
  
  if __FILE__ == $0
    # SqliteImpl.instance
    SqliteImpl.new
  end
  
end
